﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.Serialization;
using CWB.Common.Utilities;
using CWB.Common.Logging;
using CWB.Common.Constant;
using System.Data.SQLite;
using System.Data;
using CWB.Common.DTO;
using CWB.Web.Configuration;

namespace CWB.Web.DB
{
    [DataContract]
    public class DbModelCategorySentence
    {
        [DataMember(Name = "sn")]
        public int sn;
        [DataMember(Name = "c")]
        public string Category;
    }


    public class DbTableCategorySentence : BaseDAO
    {
        private readonly static ISysLog log = SysLog.GetLogger(typeof(DbTableCategorySentence));

        public DbTableCategorySentence()
        {
            base.init(typeof(DbTableCategorySentence).ToString(), DataBaseName.Sentence);
        }

        private List<DbModelCategorySentence> getModuleCallBack(SQLiteDataReader sdr)
        {
            List<DbModelCategorySentence> listD = new List<DbModelCategorySentence>() { };
            if (sdr.HasRows)
            {
                while (sdr.Read())
                {
                    listD.Add(new DbModelCategorySentence()
                    {
                        sn = Convert.ToInt32(sdr["sn"].ToString()),
                        Category = sdr["Category"].ToString()
                    });
                }
            }
            return listD;
        }

        public DbModelCategorySentence getDataBySn(int sn)
        {
            const string strCMD = "select * from Category where sn = @sn";
            List<SQLiteParameter> listPara = new List<SQLiteParameter>() { };
            listPara.Add(new SQLiteParameter("@sn", sn));
            List<DbModelCategorySentence> rtn = ExecuteReader<DbModelCategorySentence>(CommandType.Text, strCMD, listPara, getModuleCallBack);
            if (rtn.Count > 0)
            {
                return rtn[0];
            }
            else
            {
                Exception e = new Exception(string.Format("Data not found. {0}.sn : [{1}]", typeof(DbModelCategorySentence).Name, sn));
                throw (e);
            }
        }

        public DbModelCategorySentence getDataByKey(string Category)
        {
            const string strCMD = "select * from Category where Category = @Category";
            List<SQLiteParameter> listPara = new List<SQLiteParameter>() { };
            listPara.Add(new SQLiteParameter("@Category", Category));
            List<DbModelCategorySentence> rtn = ExecuteReader<DbModelCategorySentence>(CommandType.Text, strCMD, listPara, getModuleCallBack);
            if (rtn.Count > 0)
            {
                return rtn[0];
            }
            else
            {
                Exception e = new Exception(string.Format("Data not found. {0}.sn : [{1}]", typeof(DbModelCategorySentence).Name, Category));
                throw (e);
            }
        }

        public bool CheckCategoryIsExist(string category)
        {
            int nums = ExecuteReaderCount<string>("Category", "Category", category);
            if (nums == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public bool createData(DbModelCategorySentence model)
        {
            const string strCMD = "insert into Category (Category) values (@category)";
            List<SQLiteParameter> listPara = new List<SQLiteParameter>() { };
            listPara.Add(new SQLiteParameter("@category", model.Category));
            int icnt = ExecuteNonQuery(strCMD, listPara);
            return (icnt > 0);
        }

        public bool updateDataBySN(DbModelCategorySentence model)
        {
            //check is there are data existing.
            string commandText = "Select count(*) as rcnt from Category where Category = @category and sn!= @sn";
            List<SQLiteParameter> listPara = new List<SQLiteParameter>() { };
            listPara.Add(new SQLiteParameter("@category", model.Category));
            listPara.Add(new SQLiteParameter("@sn", model.sn));
            int icnt = ExecuteReaderCount(CommandType.Text, commandText, listPara);
            if (icnt == 0)
            {
                commandText = "Update Category set Category = @category where sn = @sn";
                listPara.Add(new SQLiteParameter("@category", model.Category));
                listPara.Add(new SQLiteParameter("@sn", model.sn));
                icnt = ExecuteNonQuery(commandText, listPara);
                return (icnt > 0);
            }
            else
            {
                return false;
            }
        }

        public bool DeleteDataBySn(int sn)
        {
            //Check is there are a Sentence using this category
            DbTableSentence odb = new DbTableSentence();
            Paging pg = new Paging()
            {
                pageSize = AppConfigManager.SystemSetting.UsersPageingSize,
                currentPage = 1
            };
            List<DbModelSentence> data = odb.getDataByParentSN(ref pg, sn);
            if (data.Count == 0)
            {
                const string strCMD = "delete from Category where sn = @sn";
                List<SQLiteParameter> listPara = new List<SQLiteParameter>() { };
                listPara.Add(new SQLiteParameter("@sn", sn));
                int icnt = ExecuteNonQuery(strCMD, listPara);
                return (icnt > 0);
            }
            else
            {
                return false;
            }
        }

        public List<DbModelCategorySentence> getAllData()
        {
            string sqlMain = "Select * from Category";
            List<SQLiteParameter> listPara = new List<SQLiteParameter>() { };
            //get data
            List<DbModelCategorySentence> listData = ExecuteReader<DbModelCategorySentence>(CommandType.Text, sqlMain, listPara, getModuleCallBack);
            return listData;
        }
    }
}
